#!/usr/bin/env python
# -*- coding:UTF-8 -*-
__author__ = 'wycao'

import psycopg2,time,random,hashlib
import psycopg2.pool


class PostKeyword:
    def __init__(self):
        self.dbpool = psycopg2.pool.PersistentConnectionPool(1,1,database='mypgdatabase',
                                user = 'mypguser',
                                password='mypguser',
                                host = 'localhost',
                                port = '5432')
        self.conn = self.dbpool.getconn()
        print("数据库链接成功")
        self.cur = self.conn.cursor()

    def creat_32str(self):
        nowtime = time.time()
        basestr = 'qwertyuiopasdfghjklzxcvbnmASDFGHJKLQWERTYUIOPZXCVBNM'
        str1 = "".join(random.sample(basestr, 32))
        str2 = str1 + str(nowtime)
        creatstr = hashlib.new('MD5', str2.encode()).hexdigest()
        return creatstr

    def insert_data(self,values):
        id = self.creat_32str()
        # self.cur.execute(
        #     "insert into cninfo_show_announcement (id,report_type,cmp_code,cmp_short_name,report_title,report_href,show_date,create_ts,title_key1,title_key2,title_key3) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);",(id,values[0],values[1],values[2],values[3],values[4],values[5],'Now()',values[6],values[7],values[8]))
        self.cur.execute(
            "insert into t_faf_cninfo_report values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);",(id,values[0],values[1],values[2],values[3],values[4],values[5],values[6],values[7],values[8],values[9],values[10],'Now()','Now()'))
        self.conn.commit()

    def select_count_date(self,values):
        self.cur.execute("select count(1) from t_faf_cninfo_report where categorycode = %s and companycode =%s and reportname =%s and showdate=%s ;",(values[0],values[1],values[2],values[3]))
        result = self.cur.fetchall()
        return result[0][0]

    def insert_another_date(self,values):
        id = self.creat_32str()
        self.cur.execute(
            "insert into t_faf_cninfo_report_another (id,companycode,companyname,reportname,file_url,reportname_date,reportname_type,reportname_status,create_ts,update_ts) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);",(id,values[0],values[1],values[2],values[3],values[4],values[5],values[6],'Now()','Now()'))
        self.conn.commit()

    def insert_stock_info(self,values):
        id = self.creat_32str()
        self.cur.execute("insert into cninfo_stock_info values (%s,%s,%s,%s,%s,%s,%s);",(id,values[0],values[1],values[2],values[3],'Now()','Now()'))
        self.conn.commit()

    def select_stock_info(self,offset):
        self.cur.execute("select orgid,code,shortname from public.cninfo_stock_info order by create_ts asc limit 10 offset "+str(offset*10)+";")
        result = self.cur.fetchall()
        return result

# sk = PostKeyword()
# sk.select_stock_info(10)